package com.yalu.util;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.yalu.common.Constant;

public class ReportExportUtil {

	public static String getAttributeNameById(int attributeId) {
		switch (attributeId) {
		case 1:
			return "淘宝平台自营小计";
		case 2:
			return "京东平台自营小计";
		case 3:
			return "其他第三方平台自营";
		case 4:
			return "集市分销";
		case 5:
			return "商城分销";
		case 6:
			return "入仓销售";
		case 7:
			return "平台批发";
		default:
			return "";
		}
	}

	public static int getBeginRowIndexByDateType(String dateType) {
		if (dateType.equals(Constant.WEEK_STRING)) {
			return Constant.BEGIN_ROW_INDEX_WEEK;
		} else if (dateType.equals(Constant.MONTH_STRING)) {
			return Constant.BEGIN_ROW_INDEX_MONTH;
		} else if (dateType.equals(Constant.YEAR_STRING)) {
			return Constant.BEGIN_ROW_INDEX_YEAR;
		} else if (dateType.equals(Constant.LAST_WEEK_STRING)) {
			return Constant.BEGIN_ROW_INDEX_LAST_WEEK;
		} else if (dateType.equals(Constant.LAST_YEAR_THIS_WEEK_STRING)) {
			return Constant.BEGIN_ROW_INDEX_LAST_YEAR_THIS_WEEK;
		} else if (dateType.equals(Constant.LAST_MONTH_STRING)) {
			return Constant.BEGIN_ROW_INDEX_LAST_MONTH;
		} else if (dateType.equals(Constant.LAST_YEAR_THIS_MONTH_STRING)) {
			return Constant.BEGIN_ROW_INDEX_LAST_YEAR_THIS_MONTH;
		} else if (dateType.equals(Constant.LAST_YEAR_STRING)) {
			return Constant.BEGIN_ROW_INDEX_LAST_YEAR;
		} else if (dateType.equals(Constant.LAST_YEAR_THIS_CUSTOM_STRING)) {
			return Constant.BEGIN_ROW_INDEX_LAST_YEAR_THIS_CUSTOM;
		} else if (dateType.equals(Constant.NETWORKIO_STRING)) {
			return Constant.BEGIN_ROW_INDEX_NETWORKIO;
		} else if (dateType.equals(Constant.BRAND_STRING)) {
			return Constant.BEGIN_ROW_INDEX_BRAND;
		} else if (dateType.equals(Constant.PRODUCT_YEAR_STRING)) {
			return Constant.BEGIN_ROW_INDEX_PRODUCT_YEAR;
		}else if (dateType.equals(Constant.ITEM_SALE_RANK_STRING)) {
			return Constant.BEGIN_ROW_INDEX_ITEM_SALE_RANK;
		}
		return -1;
	}

	public static String getSalesReportSql(String dateType, String beginTime,
			String endTime, String lastFlag, String compareYearStr) {
		beginTime = ReportExportUtil.formatTime("yyyyMMdd", "yyyy-MM-dd",
				beginTime);
		endTime = ReportExportUtil
				.formatTime("yyyyMMdd", "yyyy-MM-dd", endTime);
		StringBuffer sql = new StringBuffer();
		if (dateType.equals(Constant.WEEK_STRING)) {
			sql.append("SELECT * FROM v_report_week_analysis WHERE YEARWEEK('"
					+ beginTime
					+ "',7)=WEEK ORDER BY attribute_id ASC,shopid ASC;");
		} else if (dateType.equals(Constant.MONTH_STRING)) {
			sql
					.append("SELECT * FROM v_report_month_analysis WHERE DATE_FORMAT('"
							+ beginTime
							+ "','%Y%m')=MONTH ORDER BY attribute_id ASC,shopid ASC;");
		} else if (dateType.equals(Constant.YEAR_STRING)) {
			sql
					.append("SELECT * FROM v_report_year_analysis WHERE DATE_FORMAT('"
							+ beginTime
							+ "','%Y')=YEAR ORDER BY attribute_id ASC,shopid ASC;");
		} else if (dateType.equals(Constant.LAST_WEEK_STRING)) {
			if (lastFlag != null && lastFlag.equals(Constant.LAST_WEEK_STRING)) {
				sql
						.append("SELECT * FROM v_report_week_analysis WHERE YEARWEEK('"
								+ beginTime
								+ "'-INTERVAL 1 WEEK,7)=WEEK ORDER BY attribute_id ASC,shopid ASC;");
			} else {
				sql
						.append("SELECT * FROM v_report_week_analysis WHERE YEARWEEK('"
								+ beginTime
								+ "',7)=WEEK ORDER BY attribute_id ASC,shopid ASC;");
			}
		} else if (dateType.equals(Constant.LAST_YEAR_THIS_WEEK_STRING)) {
			if (lastFlag != null
					&& lastFlag.equals(Constant.LAST_YEAR_THIS_WEEK_STRING)) {
				sql
						.append("SELECT * FROM v_report_week_analysis WHERE YEARWEEK('"
								+ beginTime
								+ "'-INTERVAL 1 YEAR,7)=WEEK ORDER BY attribute_id ASC,shopid ASC;");
			} else {
				sql
						.append("SELECT * FROM v_report_week_analysis WHERE YEARWEEK('"
								+ beginTime
								+ "',7)=WEEK ORDER BY attribute_id ASC,shopid ASC;");
			}
		} else if (dateType.equals(Constant.LAST_MONTH_STRING)) {
			if (lastFlag != null && lastFlag.equals(Constant.LAST_MONTH_STRING)) {
				sql
						.append("SELECT * FROM v_report_month_analysis WHERE DATE_FORMAT('"
								+ beginTime
								+ "'- INTERVAL 1 MONTH,'%Y%m')=MONTH ORDER BY attribute_id ASC,shopid ASC;");
			} else {
				sql
						.append("SELECT * FROM v_report_month_analysis WHERE DATE_FORMAT('"
								+ beginTime
								+ "','%Y%m')=MONTH ORDER BY attribute_id ASC,shopid ASC;");
			}
		} else if (dateType.equals(Constant.LAST_YEAR_THIS_MONTH_STRING)) {
			if (lastFlag != null
					&& lastFlag.equals(Constant.LAST_YEAR_THIS_MONTH_STRING)) {
				sql
						.append("SELECT * FROM v_report_month_analysis WHERE DATE_FORMAT('"
								+ beginTime
								+ "'- INTERVAL 1 YEAR,'%Y%m')=MONTH ORDER BY attribute_id ASC,shopid ASC;");
			} else {
				sql
						.append("SELECT * FROM v_report_month_analysis WHERE DATE_FORMAT('"
								+ beginTime
								+ "','%Y%m')=MONTH ORDER BY attribute_id ASC,shopid ASC;");
			}
		} else if (dateType.equals(Constant.LAST_YEAR_STRING)) {
			if (lastFlag != null && lastFlag.equals(Constant.LAST_YEAR_STRING)) {
				sql
						.append("SELECT * FROM v_report_year_analysis WHERE DATE_FORMAT('"
								+ beginTime
								+ "'-INTERVAL 1 YEAR,'%Y')=YEAR ORDER BY attribute_id ASC,shopid ASC;");
			} else {
				sql
						.append("SELECT * FROM v_report_year_analysis WHERE DATE_FORMAT('"
								+ beginTime
								+ "','%Y')=YEAR ORDER BY attribute_id ASC,shopid ASC;");
			}
		} else if (dateType.equals(Constant.LAST_YEAR_THIS_CUSTOM_STRING)) {
			if (compareYearStr != null) {
				int currentYear = Integer.parseInt(beginTime.substring(0, 4));
				int compareYear = Integer.parseInt(compareYearStr);
				String oper = "+";
				if (currentYear > compareYear) {
					oper = "-";
				}
				int interval = currentYear - compareYear;
				sql
						.append("SELECT `attribute_id`,`shopid`,`shopname`,SUM(totalAmount) totalAmount,"
								+ "SUM(cost_amount) cost_amount,SUM(views) views,SUM(guests) guests,"
								+ "AVG(success_rate) success_rate,AVG(piecesPrice) piecesPrice,"
								+ "AVG(pieces_per_price) pieces_per_price FROM v_report_custom_analysis "
								+ "WHERE pay_time >= DATE_FORMAT('"
								+ beginTime
								+ "'"
								+ oper
								+ " INTERVAL "
								+ interval
								+ " YEAR,'%Y%m%d') "
								+ "AND pay_time <= DATE_FORMAT('"
								+ endTime
								+ "'"
								+ oper
								+ " INTERVAL "
								+ interval
								+ " YEAR,'%Y%m%d') "
								+ "GROUP BY  `attribute_id`,`shopid`,`shopname");
			} else {
				sql
						.append("SELECT `attribute_id`,`shopid`,`shopname`,SUM(totalAmount) totalAmount,"
								+ "SUM(cost_amount) cost_amount,SUM(views) views,SUM(guests) guests,"
								+ "AVG(success_rate) success_rate,AVG(piecesPrice) piecesPrice,"
								+ "AVG(pieces_per_price) pieces_per_price FROM v_report_custom_analysis "
								+ "WHERE pay_time >= DATE_FORMAT('"
								+ beginTime
								+ "' ,'%Y%m%d') "
								+ "AND pay_time <= DATE_FORMAT('"
								+ endTime
								+ "','%Y%m%d') "
								+ "GROUP BY  `attribute_id`,`shopid`,`shopname");
			}
		}
		return sql.toString();
	}

	public static String getNetworkioReportSql(String dateType,
			String beginTime, String endTime) {
		beginTime = ReportExportUtil.formatTime("yyyyMMdd", "yyyy-MM-dd",
				beginTime);
		endTime = ReportExportUtil
				.formatTime("yyyyMMdd", "yyyy-MM-dd", endTime);
		StringBuffer sql = new StringBuffer();
		if (dateType.equals(Constant.NETWORKIO_STRING)) {
			sql
					.append("SELECT * FROM  v_report_networkio_week_analysis WHERE WEEK=YEARWEEK('"
							+ beginTime
							+ "',7) AND lastweek=YEARWEEK('"
							+ beginTime
							+ "'-INTERVAL 1 WEEK,7)  ORDER BY attribute_id ASC,shopid ASC");
		}
		return sql.toString();
	}

	public static String getBrandReportSql(String dateType, String beginTime,
			String endTime) {
		StringBuffer sql = new StringBuffer();
		if (dateType.equals(Constant.BRAND_STRING)) {
			sql
					.append("SELECT brand_name,SUM(total_pieces) total_pieces,SUM(pieces_price) pieces_price,"
							+ "SUM(amount) amount,AVG(brand_per) brand_per,SUM(gross_revenue) gross_revenue,"
							+ "AVG(gross_per) gross_per,AVG(cost_accomplish) cost_accomplish,"
							+ "AVG(wh_accomplish) wh_accomplish,SUM(per_pieces_count) per_pieces_count,"
							+ "SUM(per_pieces_price) per_pieces_price FROM  v_report_brands_analysis WHERE pay_time>='"
							+ beginTime
							+ "' and pay_time<='"
							+ endTime
							+ "'  GROUP BY brand_name");
		}
		return sql.toString();
	}

	public static String getProductyearsReportSql(String dateType,
			String beginTime, String endTime) {
		beginTime = ReportExportUtil.formatTime("yyyyMMdd", "yyyy-MM-dd",
				beginTime);
		endTime = ReportExportUtil
				.formatTime("yyyyMMdd", "yyyy-MM-dd", endTime);
		StringBuffer sql = new StringBuffer();
		if (dateType.equals(Constant.PRODUCT_YEAR_STRING)) {
			sql
					.append("select N.itemno,L.pieces_price L_pieces_price,N.pieces_price N_pieces_price,IFNULL((N.pieces_price-L.pieces_price)/L.pieces_price,0) C_pieces_price,");
			sql
					.append(" L.amount L_amount,N.amount N_amount,IFNULL((N.amount-L.amount)/L.amount,0) C_amount,");
			sql
					.append(" L.gross_revenue L_gross_revenue,N.gross_revenue N_gross_revenue,N.gross_revenue-L.gross_revenue C_gross_revenue,");
			sql
					.append(" IFNULL(N.amount/NT.amount,0) per_N_amount,IFNULL(L.amount/LT.amount,0) per_L_amount,");
			sql
					.append(" L.inventory_cost L_inventory_cost,N.inventory_cost N_inventory_cost,N.inventory_cost-L.inventory_cost C_inventory_cost from");
			sql
					.append(" (select itemno,product_code,SUM(pieces_price) pieces_price,SUM(amount) amount,AVG(gross_revenue) gross_revenue,SUM(inventory_cost) inventory_cost");
			sql
					.append(" from v_report_productyears_analysis where pay_time>=DATE_FORMAT('"
							+ beginTime
							+ "','%Y%m%d') and pay_time<=DATE_FORMAT('"
							+ endTime + "','%Y%m%d') group by itemno) as N");
			sql.append(" left join");
			sql
					.append(" (select itemno,product_code,SUM(pieces_price) pieces_price,SUM(amount) amount,AVG(gross_revenue) gross_revenue,SUM(inventory_cost) inventory_cost");
			sql
					.append(" from v_report_productyears_analysis where pay_time>=DATE_FORMAT('"
							+ beginTime
							+ "' -INTERVAL 1 YEAR,'%Y%m%d') and pay_time<=DATE_FORMAT('"
							+ endTime
							+ "'-INTERVAL 1 YEAR,'%Y%m%d') group by itemno) as L  on N.itemno=L.itemno");
			sql.append(" left join");
			sql
					.append(" (select product_code,SUM(amount) amount from v_report_productyears_analysis");
			sql
					.append(" where pay_time>=DATE_FORMAT('"
							+ beginTime
							+ "','%Y%m%d') and pay_time<=DATE_FORMAT('"
							+ endTime
							+ "','%Y%m%d') group by product_code) as NT on N.product_code=NT.product_code");
			sql.append(" left join");
			sql
					.append(" (select product_code,SUM(amount) amount from v_report_productyears_analysis");
			sql
					.append(" where pay_time>=DATE_FORMAT('"
							+ beginTime
							+ "' -INTERVAL 1 YEAR,'%Y%m%d') and pay_time<=DATE_FORMAT('"
							+ endTime
							+ "'-INTERVAL 1 YEAR,'%Y%m%d') group by product_code) as LT on L.product_code=LT.product_code");

		}
		return sql.toString();
	}

	public static String getItemsalerankReportSql(String dateType,
			String beginTime, String endTime) {
		StringBuffer sql = new StringBuffer();
		if (dateType.equals(Constant.ITEM_SALE_RANK_STRING)) {
			sql.append("SELECT  A.pro_code,");
			sql.append(" A.brand_name,");
			sql.append(" A.YEAR,");
			sql.append(" A.sort_name,");
			sql.append(" B.total_pieces L_total_pieces,");
			sql.append(" A.total_pieces,");
			sql
					.append(" IFNULL((A.total_pieces-B.total_pieces)/B.total_pieces,0) C_total_pieces,");
			sql.append(" A.sell_price,");
			sql.append("  A.retail_price,");
			sql.append(" A.gross_per");
			sql
					.append(" FROM (SELECT * FROM v_report_itemsalerank_analysis WHERE WEEK=YEARWEEK('"+beginTime+"',7))  AS A");
			sql
					.append(" left JOIN (SELECT  pro_code,`week`,total_pieces FROM v_report_itemsalerank_analysis WHERE WEEK=YEARWEEK('"+beginTime+"'-INTERVAL 1 WEEK,7)) AS B");
			sql.append(" ON A.pro_code=B.pro_code");
		}
		return sql.toString();
	}

	public static String formatTime(String pattern, String returnPattern,
			String time) {
		String returnStr = "";
		try {
			DateFormat format = new SimpleDateFormat(pattern);
			DateFormat returnFormat = new SimpleDateFormat(returnPattern);
			Date date = format.parse(time);
			returnStr = returnFormat.format(date);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return returnStr;
	}

	// public static String getLastBeginTime(String chooseTime, String dateType)
	// {
	// return getLastBeginTime(chooseTime, dateType, "yyyyMMdd", "yyyyMMdd");
	// }
	//
	// public static String getLastxBeginTime(String chooseTime, String
	// dateType,
	// String pattern, String returnPattern) {
	// String beginTime = "";
	// try {
	// DateFormat format = new SimpleDateFormat(pattern);
	// DateFormat returnFormat = new SimpleDateFormat(returnPattern);
	// Date date = format.parse(chooseTime);
	// beginTime = "";
	// Calendar calendar = new GregorianCalendar();
	// calendar.setTime(date);
	// if (dateType.equals(LAST_WEEK_STRING)) {
	// calendar.add(Calendar.DATE, -7); // 减一年
	// beginTime = returnFormat.format(calendar.getTime());
	// } else if (dateType.equals(LAST_MONTH_STRING)) {
	// calendar.add(Calendar.MONTH, -1); // 减一年
	// calendar.set(Calendar.DAY_OF_MONTH, 1);
	// beginTime = returnFormat.format(calendar.getTime());
	// } else if (dateType.equals(LAST_YEAR_THIS_MONTH_STRING)) {
	// calendar.add(Calendar.YEAR, -1); // 减一年
	// calendar.set(Calendar.DAY_OF_MONTH, 1);
	// beginTime = returnFormat.format(calendar.getTime());
	// } else if (dateType.equals(LAST_YEAR_STRING)) {
	// calendar.set(Calendar.DAY_OF_YEAR, 1);
	// calendar.add(Calendar.YEAR, -1); // 减一年
	// calendar.set(Calendar.DATE, 1); // 设置为该月第一天
	// beginTime = returnFormat.format(calendar.getTime());
	// } else {
	// beginTime = chooseTime;
	// }
	// } catch (ParseException e) {
	// e.printStackTrace();
	// }
	// return beginTime;
	// }
	//
	// public static String getLastEndTime(String chooseTime, String dateType) {
	// return getLastEndTime(chooseTime, dateType, "yyyyMMdd", "yyyyMMdd");
	// }
	//
	// public static String getLastEndTime(String chooseTime, String dateType,
	// String pattern, String returnPattern) {
	// String endTime = "";
	// try {
	// DateFormat format = new SimpleDateFormat(pattern);
	// DateFormat returnFormat = new SimpleDateFormat(returnPattern);
	// Date date = format.parse(chooseTime);
	// endTime = "";
	// Calendar calendar = new GregorianCalendar();
	// calendar.setTime(date);
	// if (dateType.equals(LAST_WEEK_STRING)) {
	// calendar.add(Calendar.DATE, -7); // 减一年
	// endTime = returnFormat.format(calendar.getTime());
	// } else if (dateType.equals(LAST_MONTH_STRING)) {
	// calendar.set(Calendar.DATE, 1); // 设置为该月第一天
	// calendar.add(Calendar.DATE, -1); // 再减一天即为上个月最后一天
	// endTime = returnFormat.format(calendar.getTime());
	// } else if (dateType.equals(LAST_YEAR_THIS_MONTH_STRING)) {
	// calendar.add(Calendar.MONTH, 1); // 加一个月
	// calendar.set(Calendar.DATE, 1); // 设置为该月第一天
	// calendar.add(Calendar.YEAR, -1); // 再减一年
	// calendar.add(Calendar.DATE, -1); // 再减一天即为上个月最后一天
	// endTime = returnFormat.format(calendar.getTime());
	// } else if (dateType.equals(LAST_YEAR_STRING)) {
	// calendar.set(Calendar.DAY_OF_YEAR, 1);
	// calendar.add(Calendar.DATE, -1); // 再减一天即为去年最后一天
	// endTime = returnFormat.format(calendar.getTime());
	// } else {
	// endTime = chooseTime;
	// }
	// } catch (ParseException e) {
	// e.printStackTrace();
	// }
	// return endTime;
	// }
	//
	// public static void main(String[] args) throws ParseException {
	// String chooseTime = "20120229";
	// DateFormat format = new SimpleDateFormat("yyyyMMdd");
	// Date date = format.parse(chooseTime);
	// Calendar calendar = new GregorianCalendar();
	// calendar.setTime(date);
	// }

}
